set more off
set scheme dklean
tempfile buffer

*===============================================================================
* Combined do file for tables
*===============================================================================

cd "./"

*=====
*Define functions

*t-test program
capt prog drop ttestsum
program ttestsum, eclass
	version 14.0
	syntax varlist [if], bvar(varname)
	
	tempname mu1 mu2 tstat
	
	marksample touse
	local q = 1
	foreach var of local varlist {
		qui ttest `var' if `touse', by(`bvar') unequal
		mat `mu1' = nullmat(`mu1'), r(mu_1)
		mat `mu2' = nullmat(`mu2'), r(mu_2)
		mat `tstat' = nullmat(`tstat'), r(t)
	}
	
	foreach mat in mu1 mu2 tstat {
		mat coln ``mat'' = `varlist'
	}
	
	eret local cmd "ttestsum"
	foreach mat in mu1 mu2 tstat {
		eret mat `mat' = ``mat''
	}
	
	qui sum assets if `touse'
	eret scalar N = `r(N)'

end

*Count number of insurers
capt prog drop count_instances
program count_instances
	version 14.0
	syntax varname [if], sname(string)
	
	marksample touse
	egen NO = count(`varlist') if `touse', by(`varlist')
	replace NO = 0 if !`touse'
	replace NO = 1/NO
	egen tot = total(NO)
	estadd scalar `sname' = tot[_N]
	drop NO tot
	
end

*Double cluster SEs with areg
capt prog drop thompson_areg
program thompson_areg, eclass
	version 13.1
    syntax anything [if], clusd1(varname) clusd2(varname) avar(varname)
	 
	tempname varro vard1 vard2 doubleclus
	
	marksample touse
	areg `anything' if `touse', absorb(`avar') vce(robust)
	mat `varro' = e(V)
	
	qui areg `anything' if `touse', absorb(`avar') vce(cluster `clusd1')
	mat `vard1' = e(V)
	
	qui areg `anything' if `touse', absorb(`avar') vce(cluster `clusd2')
	mat `vard2' = e(V)
	
	mat `doubleclus' = `vard1' + `vard2' - `varro'
	
	ereturn repost V = `doubleclus'

end

*==========================
*==========================
* For Table 1: Bond portfolio by asset class (2014)
*=====

*From PE Share Prep.do
use Data/Asset_Mix_Prep.dta, clear

keep if quarter(dofq(Quarter))==4
gen year = yofd(dofq(Quarter))

ren NAIC_Code naic
xtset naic year

merge 1:1 naic year using Data/Pe_status.dta, nogen keep(match)
drop test

*Convert assets to $BN
replace assets = assets/1E6
format assets %9.1fc

save `buffer'

use `buffer', clear

*Summary stats for 2014
keep if year == 2014

label var SAClass5 "Private-label ABS"

gen GenBondShare = TotalFV/assets*100

winsor2 GenBondShare SAClass* SNCat*, replace cuts (5 95) by(pe_status)

label var GenBondShare "General account bonds/Total assets"

bys pe_status: sum GenBondShare
bys pe_status: sum SAClass*
bys pe_status: sum SNCat*

gen pe_inv = pe_status==0

*Construct Table 1: Overall Summary

eststo clear
ttestsum GenBondShare SAClass* SNCat*, bvar(pe_inv)
esttab using Output/Overall_Summary.tex, /// 
booktabs label fragment nomtitle nonumbers width(\textwidth) replace ///
cells("mu1(fmt(%9.1fc)) mu2(fmt(%9.1fc)) tstat(fmt(%9.2fc))") ///
substitute( "mu1" "PE" "mu2" "Non-PE" "tstat" "\$t\$-stat" ///
"Corporate" "\midrule Corporate" "NAIC 1" "\midrule NAIC 1") ///
stats(N, fmt(%9.0fc) label("Insurers"))

*==========================
*==========================
* For Table 2, 3, 4, 5, 7
cls
tempfile buffer
*=====

use Data/Asset_Mix_Prep.dta, clear

label var SAClass5 "Private-label ABS"

label var mutual "Mutual Status"
xtset NAIC_Code Quarter

*Define PE groups, data has been anonymized 
label define PE_ID 1 "PE Group A" 2 "PE Group B" 3 "PE Group C" 4 "Other PE" 5 "Non PE"
label values PE_ID PE_ID

*General account bonds
table PE_ID if Quarter==tq(2014q4), c(sum TotalFV) f(%9.1fc) row

label var pe_status "PEiq"

*Drop if always PE
egen cQ = count(Quarter), by(NAIC_Code)
egen TPE = total(pe_status), by(NAIC_Code)
drop if (cQ==TPE & pe_status==1)
drop cQ TPE

gen lntotFV = ln(TotalFV)
gen BondGrowth = 100*(lntotFV-L.lntotFV)

label var BondGrowth "$\Delta \ln$ Bonds"

gen Group_A = 0
replace Group_A = 1 if PE_ID==1

gen Group_B = 0
replace Group_B = 1 if PE_ID==2

gen Group_C = 0
replace Group_C = 1 if PE_ID==3

gen Other_PE = 0
replace Other_PE = 1 if PE_ID==4

label var Group_A "PE Group A"
label var Group_B "PE Group B"
label var Group_C "PE Group C "
label var Other_PE "Other PE"

*Winsorize full shares
winsor2 SAClass* SNCat* YTMAgg YTMAClass* DiscountAClass*, replace cuts (5 95) by(pe_status)
winsor2 BondGrowth, replace cuts (5 95) by(pe_status)

gen Dum10_14 = inrange(Quarter,tq(2010q1),tq(2014q4))
label var Dum10_14 "2010-2014"

*=====
*Regression by asset class

*Clear previous results
cls

*=====
*Construct Table 2: Bond portfolio by asset class

eststo clear

foreach depvar of varlist SAClass1 SAClass2 SAClass5 SAClass6 SAClass3 SAClass4 {
	
	qui {
		thompson_areg `depvar' Dum10_14 pe_status i.Quarter, ///
		avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)
		su `depvar'
		estadd scalar meandep = r(mean)
		estadd local QFE "Y", replace
		estadd local IFE "Y", replace
		estadd local cluster "I+Q", replace
		count_instances NAIC_Code, sname(num_ins)
		count_instances NAIC_Code if pe_status==1, sname(num_pe)
		eststo
	}

}

esttab using Output/SAClassTrends.tex, booktabs replace fragment ///
nocon nonotes label nonum b(%9.1f) se(%9.2f) keep(Dum10_14 pe_status) ///
stats(meandep QFE IFE cluster r2 N num_ins num_pe, /// 
fmt(%9.1fc %1s %1s %9s %9.2fc %9.0fc %9.0fc %9.0fc) ///
labels("Mean of dep var" "Quarter FE" "Insurer FE" "SE clustered by" "$ R^2$" "Insurer-Quarters" /// 
"Insurers" "PE insurers")) substitute("PEiq" "$ PE_{i,q}")

*=====
*Construct Table 3: Bond portfolio by NAIC category

eststo clear

foreach depvar of varlist SNCat1 SNCat2 SNCat3 SNCat4 SNCat5 SNCat6 {
	
	qui {
		thompson_areg `depvar' pe_status i.Quarter, ///
		avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)
		su `depvar'
		estadd scalar meandep = r(mean)
		estadd local QFE "Y", replace
		estadd local IFE "Y", replace
		estadd local cluster "I+Q", replace
		count_instances NAIC_Code, sname(num_ins)
		count_instances NAIC_Code if pe_status==1, sname(num_pe)
		eststo
	}

}

esttab using Output/SNCat.tex, booktabs replace fragment ///
nocon nonotes label nonum b(%9.1f) se(%9.2f) keep(pe_status) ///
stats(meandep QFE IFE cluster r2 N num_ins num_pe, /// 
fmt(%9.1fc %1s %1s %9s %9.2fc %9.0fc %9.0fc %9.0fc) ///
labels("Mean of dep var" "Quarter FE" "Insurer FE" "SE clustered by" "$ R^2$" "Insurer-Quarters" /// 
"Insurers" "PE Insurers")) substitute("PEiq" "$ PE_{i,q}")


save `buffer'

*=====
*Construct Table 4: Private-label ABS portfolio by NAIC category

keep if !mi(TotalPLABSFV) & Quarter>=tq(2007q4)

*Winsorize ABS shares
winsor2 PLABSSNCat* PLABSSANCat* PLABSDiscNCAT*, replace cuts (5 95) by(pe_status)

egen cQ = count(Quarter), by(NAIC_Code)
egen TPE = total(pe_status), by(NAIC_Code)
drop if (cQ==TPE & pe_status==1)
drop cQ TPE

*Panel A: Reported NAIC Category

eststo clear

foreach depvar of varlist PLABSSNCat1 PLABSSNCat2 PLABSSNCat3 PLABSSNCat4 PLABSSNCat5 PLABSSNCat6 {
	
	qui {
		thompson_areg `depvar' pe_status i.Quarter, ///
		avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)
		su `depvar'
		estadd scalar meandep = r(mean)
		estadd local QFE "Y", replace
		estadd local IFE "Y", replace
		estadd local cluster "I+Q", replace
		count_instances NAIC_Code, sname(num_ins)
		count_instances NAIC_Code if pe_status==1, sname(num_pe)
		eststo
	}

}

esttab using Output/ABSSNCat.tex, booktabs replace fragment ///
nocon nonotes label nonum b(%9.1f) se(%9.2f) keep(pe_status) ///
stats(meandep QFE IFE cluster r2 N num_ins num_pe, /// 
fmt(%9.1fc %1s %1s %9s %9.2fc %9.0fc %9.0fc %9.0fc) ///
labels("Mean of dep var" "Quarter FE" "Insurer FE" "SE clustered by" "$ R^2$" "Insurer-Quarters" /// 
"Insurers" "PE Insurers")) substitute("PEiq" "$ PE_{i,q}")

*Panel B: Alternate NAIC Category

eststo clear

foreach depvar of varlist PLABSSANCat1 PLABSSANCat2 PLABSSANCat3 PLABSSANCat4 PLABSSANCat5 PLABSSANCat6 {
	
	qui {
		thompson_areg `depvar' pe_status i.Quarter, ///
		avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)
		su `depvar'
		estadd scalar meandep = r(mean)
		estadd local QFE "Y", replace
		estadd local IFE "Y", replace
		estadd local cluster "I+Q", replace
		count_instances NAIC_Code, sname(num_ins)
		count_instances NAIC_Code if pe_status==1, sname(num_pe)
		eststo
	}

}

esttab using Output/ABSSANCat.tex, booktabs replace fragment ///
nocon nonotes label nonum b(%9.1f) se(%9.2f) keep(pe_status) ///
stats(meandep QFE IFE cluster r2 N num_ins num_pe, /// 
fmt(%9.1fc %1s %1s %9s %9.2fc %9.0fc %9.0fc %9.0fc) ///
labels("Mean of dep var" "Quarter FE" "Insurer FE" "SE clustered by" "$ R^2$" "Insurer-Quarters" /// 
"Insurers" "PE Insurers")) substitute("PEiq" "$ PE_{i,q}")

*=====
*Construct Table 5: Private-label ABS portfolio by alternate NAIC category and time period

eststo clear

foreach depvar of varlist PLABSSANCat1 PLABSSANCat2 PLABSSANCat3 PLABSSANCat4 PLABSSANCat5 PLABSSANCat6 {
	
	qui {
		thompson_areg `depvar' Dum10_14 pe_status i.Quarter, ///
		avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)
		su `depvar'
		estadd scalar meandep = r(mean)
		estadd local QFE "Y", replace
		estadd local IFE "Y", replace
		estadd local cluster "I+Q", replace
		count_instances NAIC_Code, sname(num_ins)
		count_instances NAIC_Code if pe_status==1, sname(num_pe)
		eststo
	}

}

esttab using Output/ABSSANCatTrends.tex, booktabs replace fragment ///
nocon nonotes label nonum b(%9.1f) se(%9.2f) keep(Dum10_14 pe_status) ///
stats(meandep QFE IFE cluster r2 N num_ins num_pe, /// 
fmt(%9.1fc %1s %1s %9s %9.2fc %9.0fc %9.0fc %9.0fc %9.0fc) ///
labels("Mean of dep var" "Quarter FE" "Insurer FE" "SE clustered by" "$ R^2$" "Insurer-Quarters" /// 
"Insurers" "PE Insurers")) substitute("PEiq" "$ PE_{i,q}")

*=====
*Construct Table 7: Reduction in capital requirement due to change in treatment of private-label ABS

gen RBC_Share = RBC_Reduction/(Req_RBC+RBC_Reduction)*100
label var RBC_Share "R1 Reduction"

gen RBC_FVShare = RBC_Reduction/TotalFV*1E4
label var RBC_FVShare "RBC Reduction/Assets"

*Winsorize
winsor2 RBC_Share RBC_FVShare, replace cuts (5 95) by(pe_status)

*Full sample and split within PE for RBC/Req RBC

eststo clear

qui {

thompson_areg RBC_Share pe_status i.Quarter, ///
avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)
su RBC_Share
estadd scalar meandep = r(mean)
estadd local QFE "Y", replace
estadd local IFE "Y", replace
estadd local cluster "I+Q", replace
count_instances NAIC_Code, sname(num_ins)
count_instances NAIC_Code if pe_status==1, sname(num_pe)
eststo

}

qui {

thompson_areg RBC_Share Group_A Group_B Group_C Other_PE i.Quarter, ///
avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)
su RBC_Share
estadd scalar meandep = r(mean)
estadd local QFE "Y", replace
estadd local IFE "Y", replace
estadd local cluster "I+Q", replace
count_instances NAIC_Code, sname(num_ins)
count_instances NAIC_Code if PE_ID==1, sname(num_AP)
count_instances NAIC_Code if PE_ID==2, sname(num_GA)
count_instances NAIC_Code if PE_ID==3, sname(num_GG)
count_instances NAIC_Code if PE_ID==4, sname(num_ope)
eststo

}


esttab using Output/RBCRed.tex, booktabs replace fragment ///
nocon nonotes label nonum b(%9.1f) se(%9.2f) /// 
keep(pe_status Group_A Group_B Group_C Other_PE) ///
stats(meandep QFE IFE cluster r2 N num_ins num_pe num_AP num_GA num_GG num_ope, /// 
fmt(%9.1fc %1s %1s %9s %9.2fc %9.0fc %9.0fc %9.0fc %9.0fc %9.0fc %9.0fc %9.0fc) ///
labels("Mean of dep var" "Quarter FE" "Insurer FE" "SE clustered by" "$ R^2$" "Insurer-Quarters" /// 
"Insurers" "PE Insurers" "PE Group A" "PE Group B" "PE Group C" ///
"Other PE Insurers")) substitute("R1" "$ R_1$" "PEiq" "$ PE_{i,q}")


*==========================
*==========================
* For Table 6: Realized returns on corporate bonds
cls
tempfile buffer
*===

***********
*Returns data

import delimited using "Data/all.bonds.portfolio.returns.csv", varn(1) clear

ren v1 date
gen td = date(date,"YMD")
format td %td

gen month = mofd(td)
format month %tm

destring pe nonpe, force replace
drop if mi(pe,nonpe)

keep month pe nonpe

save `buffer'

***********
*Factor data

import delimited using "Data/factor.data.csv", varn(1) clear

ren v1 date
gen td = date(date,"YMD")
format td %td

gen month = mofd(td)
format month %tm

drop date td
order month

merge 1:1 month using `buffer', nogen keep(match)

*****
* Excess returns

foreach v of varlist pe nonpe mktret {
	gen `v'_exc = `v' -  rf
}

***********
* Construct Table 6: Realized returns on corporate bonds

foreach v of varlist pe_exc nonpe_exc mktret_exc defaultspread termspread liqtraded liqinnov {
	replace `v' = `v'*100
}

*IG-HY
replace defaultspread = -defaultspread

gen pespread_exc = pe_exc-nonpe_exc

label var pe_exc "High-PE"
label var nonpe_exc "Low-PE"
label var pespread_exc "High PE - Low PE"
label var mktret_exc "Stock market excess return"
label var defaultspread "Default premium"
label var termspread "Term spread"
label var liqinnov "Liquidity factor"

tsset month

eststo clear

qui{
eststo: reg pe_exc defaultspread termspread, vce(ro)
eststo: reg pe_exc mktret_exc defaultspread termspread, vce(ro)
eststo: reg pe_exc defaultspread termspread liqinnov, vce(ro)
eststo: reg nonpe_exc defaultspread termspread, vce(ro)
eststo: reg nonpe_exc mktret_exc defaultspread termspread, vce(ro)
eststo: reg nonpe_exc defaultspread termspread liqinnov, vce(ro)
}

esttab using Output/CorpBond_Returns.tex, booktabs replace fragment ///
nonotes label nonum b(%9.1f) se(%9.1f) ///
order(_cons mktret_exc defaultspread termspread) ///
stats(r2 N, fmt(%9.2fc %9.0fc) labels("$ R^2$" "Months")) ///
substitute("Constant" "$ \alpha$")

*==========================
*==========================
* For Table 8: PE-owned insurers on the risk-return frontier
cls
tempfile buffer

*===
use Data/Asset_Mix_Prep.dta, clear

keep if quarter(dofq(Quarter))==4
gen Year = yofd(dofq(Quarter))

label define pe_status 0 "Non-PE" 1 "PE"
label values pe_status pe_status

keep Year NAIC_Code pe_status

save `buffer'

*******
*Bond investment return

import excel using "Data/Investment_income.xlsx", sheet("Sheet1") cellrange(A4) clear

drop A C D E

ren (B F-K) (Year NAIC_Code Adm_Bonds Inc_USGovt Inc_TaxExmptBonds Inc_OthBonds Inc_AffBonds)

*Destring and convert to $MN
foreach v of varlist Adm_Bonds-Inc_AffBonds {
	capture replace `v'="" if indexnot(`v',"0123456789.-")
	capture destring `v', replace
	replace `v' = `v'/1E3
}

replace Year = regexr(Year,"Y","")
destring Year, replace

drop if mi(Adm_Bonds)|Adm_Bonds==0

gen Bond_InvInc = Inc_USGovt+Inc_TaxExmptBonds+Inc_OthBonds+Inc_AffBonds

gen Bond_Yield = Bond_InvInc/Adm_Bonds*100

format Adm_Bonds-Bond_Yield %9.1fc

keep Year NAIC_Code Bond_Yield

tempfile Yield
save `Yield'

*Pull in TAC
import excel using "Data/Insurer_chars.xlsx", sheet("Sheet1") cellrange(A4) clear

keep if C=="PY(0)"

keep B F G-N
ren (B F G-N) (Year NAIC_Code Assets Liab_ex_sep Liabilities /// 
Reserves_GenAcct Reserves_Annuity Net_Income TAC ACL_RBC)

replace Year = regexr(Year,"Y","")
destring Year, replace

*Destring and convert to $BN
foreach v of varlist Assets-ACL_RBC {
	*indexnot finds strings that have characters outside a set
	capture replace `v'="" if indexnot(`v',"0123456789.-")
	capture destring `v', replace
	replace `v' = `v'/1E6
}

keep if inrange(Year,2009,2014)
keep Year NAIC_Code TAC
format TAC %9.2fc

merge 1:1 Year NAIC_Code using `Yield', nogen keep(match)

merge 1:1 Year NAIC_Code using Data/Avg_Loss_Weight.dta, nogen keep(match)
drop if mi(TAC)

gen EL_TAC = Avg_Loss_Weight*Bond_FV/TAC*100
format EL_TAC %9.0fc

merge 1:1 Year NAIC_Code using `buffer', nogen keep(match)


**********
*Construct Table 8: PE-owned insurers on the risk-return frontier

tsset NAIC_Code Year
winsor2 Bond_Yield EL_TAC, replace cuts (5 95) by(pe_status)
replace Bond_Yield = 7 if Bond_Yield>7
replace Bond_Yield = Bond_Yield*100
replace EL_TAC = 500 if EL_TAC>500
replace EL_TAC = EL_TAC/100

gen EL_TACx100 = EL_TAC*100
label var EL_TACx100 "ELoss/Capital $\times$ 100"

gen ln_Bond_FV = ln(Bond_FV)
gen EL_TAC_sq = EL_TAC*EL_TAC

label var pe_status "PEiq"
label var Bond_Yield "Bond portfolio return"
label var EL_TAC "ELoss/Capital"
label var EL_TAC_sq "(ELoss/Capital)$^2$"

eststo clear

qui {

reghdfe Bond_Yield EL_TAC EL_TAC_sq ln_Bond_FV, a(Year NAIC_Code) vce(cluster NAIC_Code Year)
su Bond_Yield
estadd scalar meandep = r(mean)
estadd local YFE "Y", replace
estadd local IFE "Y", replace
estadd local cluster "I+Q", replace
count_instances NAIC_Code, sname(num_ins)
count_instances NAIC_Code if pe_status==1, sname(num_pe)
eststo

reghdfe EL_TACx100 pe_status ln_Bond_FV, a(Year NAIC_Code) vce(cluster NAIC_Code Year)
su EL_TACx100
estadd scalar meandep = r(mean)
estadd local YFE "Y", replace
estadd local IFE "Y", replace
estadd local cluster "I+Q", replace
count_instances NAIC_Code, sname(num_ins)
count_instances NAIC_Code if pe_status==1, sname(num_pe)
eststo

reghdfe Bond_Yield EL_TAC EL_TAC_sq pe_status ln_Bond_FV, a(Year NAIC_Code) vce(cluster NAIC_Code Year)
su Bond_Yield
estadd scalar meandep = r(mean)
estadd local YFE "Y", replace
estadd local IFE "Y", replace
estadd local cluster "I+Q", replace
count_instances NAIC_Code, sname(num_ins)
count_instances NAIC_Code if pe_status==1, sname(num_pe)
eststo

}

esttab using Output/Risk_return.tex, booktabs replace fragment drop(ln_Bond_FV _cons) nocon nonotes label nonum b(%9.1f) se(%9.1f) stats(meandep YFE IFE cluster r2 N num_ins num_pe, fmt(%9.0fc %1s %1s %9s %9.2fc %9.0fc %9.0fc %9.0fc) labels("Mean of dep var" "Year FE" "Insurer FE" "SE clustered by" "$ R^2$" "Insurer-Years" "Insurers" "PE insurers")) substitute("PEiq" "$ PE_{i,q}")


